using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using fuzhu;
using LKU8.shoukuan;
using System.ComponentModel;
using System.Text.RegularExpressions;
using NPOI.SS.Util;

namespace fuzhu
{
    /// <summary>
    /// 万吉Rd10完工Excel导入助手
    /// 专门处理万吉Rd10完工Excel模板的导入
    /// </summary>
    public class WanJiRd10ExcelImportHelper_HouDao
    {
        private static WriteLog wl = new WriteLog();

        /// <summary>
        /// 从Excel文件导入万吉Rd10数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>导入的数据列表</returns>
        public static BindingList<WanJi_Rd10_View> ImportFromExcel(string filePath)
        {
            return ImportFromExcel(filePath, 0); // 默认使用第一个工作表
        }

        /// <summary>
        /// 从Excel文件的指定工作表导入万吉Rd10数据
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="sheetIndex">工作表索引</param>
        /// <returns>导入的数据列表</returns>
        public static BindingList<WanJi_Rd10_View> ImportFromExcel(string filePath, int sheetIndex)
        {
            BindingList<WanJi_Rd10_View> resultList = new BindingList<WanJi_Rd10_View>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                if (sheetIndex >= workbook.NumberOfSheets)
                {
                    throw new Exception(string.Format("工作表索引 {0} 超出范围，文件只有 {1} 个工作表", sheetIndex, workbook.NumberOfSheets));
                }

                ISheet sheet = workbook.GetSheetAt(sheetIndex);
                
                if (sheet == null)
                {
                    throw new Exception("Excel文件中没有找到工作表");
                }

                // 获取标题行（第二行，索引为0）
                IRow headerRow = sheet.GetRow(1);
                if (headerRow == null)
                {
                    throw new Exception("Excel文件中没有找到标题行（第二行）");
                }
                IRow headerRow0 = sheet.GetRow(0);
                // 解析标题行，建立列名到列索引的映射
                Dictionary<string, int> columnMapping = ParseHeaderRow(headerRow,headerRow0);

                // 验证必需的列是否存在
                ValidateRequiredColumns(columnMapping);

                // 从第二行开始读取数据（索引为1）
                int rowIndex = 2;
                int serialNumber = 1;

                while (rowIndex <= sheet.LastRowNum)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    if (row == null)
                    {
                        rowIndex++;
                        continue;
                    }

                    // 检查工单号是否为空，如果为空则停止读取
                    string moCode = GetCellValue(row, columnMapping, "工单");
                    if (string.IsNullOrEmpty(moCode))
                    {
                        break; // 工单号为空，停止读取
                    }

                    // 创建Rd10视图对象
                    WanJi_Rd10_View rd10View = new WanJi_Rd10_View();
                    
                    // 设置序号
                    rd10View.cRowNo = serialNumber;
                    
                    // 填充数据
                    FillRd10Data(rd10View, row, columnMapping);
                    
                    resultList.Add(rd10View);
                    
                    rowIndex++;
                    serialNumber++;
                }

                return resultList;
            }
            catch (Exception ex)
            {
                wl.WriteLogs("导入Excel时发生错误: " + ex.Message);
                throw new Exception("导入Excel文件失败：" + ex.Message, ex);
            }
        }

        /// <summary>
        /// 解析标题行，建立列名到列索引的映射
        /// </summary>
        /// <param name="headerRow">标题行</param>
        /// <returns>列名到列索引的映射字典</returns>
        private static Dictionary<string, int> ParseHeaderRow(IRow headerRow,IRow headerRow0)
        {
            Dictionary<string, int> columnMapping = new Dictionary<string, int>();
            
            for (int i = 0; i <= headerRow.LastCellNum; i++)
            {
                ICell cell = headerRow.GetCell(i);
                if (cell != null)
                {
                    string cellValue = cell.ToString().Trim();
                   //如果单元格等于空，取第一行的
                    if (string.IsNullOrEmpty(cellValue))
                    {
                        cellValue = headerRow0.GetCell(i).ToString().Trim();
                    }

                    if (!string.IsNullOrEmpty(cellValue))
                    {
                        // 直接映射列名
                        columnMapping[cellValue] = i;
                        
                        // 为了兼容性，添加一些别名映射
                        switch (cellValue)
                        {
                            case "日期":
                                columnMapping["日期"] = i;
                                break;
                            case "线别": 
                                columnMapping["线别"] = i;
                                break;
                            case "班别":
                                columnMapping["班别"] = i;
                                break;
                            case "U8编码":
                                columnMapping["U8编码"] = i;
                                columnMapping["U8存货编码"] = i;
                                break;
                            case "工单":
                                columnMapping["工单"] = i;
                                columnMapping["工单号"] = i;
                                break;
                            case "排产计划编号":
                                columnMapping["排产计划编号"] = i;
                                columnMapping["计划单号"] = i;
                                break;
                            case "实际生产数":
                                columnMapping["实际生产数"] = i;
                                columnMapping["生产数量"] = i;
                                break;
                            case "线上":
                                columnMapping["线上"] = i;
                                break;
                            case "线外":
                                columnMapping["线外"] = i;
                                break;
                            case "线体":
                                columnMapping["线体"] = i;
                                break;
                            case "时间":
                                columnMapping["时间"] = i;
                                columnMapping["设备工时"] = i;
                                break;
                            case "工时":
                                columnMapping["工时"] = i;
                                columnMapping["人工工时"] = i;
                                break;
                        }
                    }
                }
            }

            return columnMapping;
        }

        /// <summary>
        /// 验证必需的列是否存在
        /// </summary>
        /// <param name="columnMapping">列名映射</param>
        private static void ValidateRequiredColumns(Dictionary<string, int> columnMapping)
        {
            string[] requiredColumns = {   "线体",  "工单", "实际生产数", "线上", "线外", "时间", "工时" };
            List<string> missingColumns = new List<string>();

            foreach (string required in requiredColumns)
            {
                if (!columnMapping.ContainsKey(required))
                {
                    missingColumns.Add(required);
                }
            }

            if (missingColumns.Count > 0)
            {
                throw new Exception("Excel模板缺少必需的列：" + string.Join(", ", missingColumns.ToArray()));
            }
        }

        /// <summary>
        /// 从行中获取指定列的值
        /// </summary>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        /// <param name="columnName">列名</param>
        /// <returns>单元格值</returns>
        private static string GetCellValue(IRow row, Dictionary<string, int> columnMapping, string columnName)
        {
            if (!columnMapping.ContainsKey(columnName))
            {
                return "";
            }

            int columnIndex = columnMapping[columnName];
            ICell cell = row.GetCell(columnIndex);
            
            if (cell == null)
            {
                return "";
            }

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        return cell.StringCellValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue.ToString();
                    case CellType.FORMULA:
                        // 改进公式处理逻辑
                        return GetFormulaValue(cell);
                    case CellType.BLANK:
                        return "";
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("单元格错误: {0}", cell.ErrorCellValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("获取单元格值错误: {0}", ex.Message));
                return "";
            }
        }

        /// <summary>
        /// 获取公式单元格的值
        /// </summary>
        /// <param name="cell">公式单元格</param>
        /// <returns>公式计算结果</returns>
        private static string GetFormulaValue(ICell cell)
        {
            try
            {
                // 尝试获取公式的计算结果
                IFormulaEvaluator evaluator = cell.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
                CellValue cellValue = evaluator.Evaluate(cell);
                
                switch (cellValue.CellType)
                {
                    case CellType.STRING:
                        return cellValue.StringValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return DateUtil.GetJavaDate(cellValue.NumberValue).ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cellValue.NumberValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cellValue.BooleanValue.ToString();
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("公式计算错误: {0}", cellValue.ErrorValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("公式计算异常: {0}", ex.Message));
                
                // 如果公式计算失败，尝试获取缓存结果
                try
                {
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.STRING:
                            return cell.StringCellValue.Trim();
                        case CellType.NUMERIC:
                            if (DateUtil.IsCellDateFormatted(cell))
                            {
                                return cell.DateCellValue.ToString("yyyy-MM-dd");
                            }
                            else
                            {
                                return cell.NumericCellValue.ToString();
                            }
                        case CellType.BOOLEAN:
                            return cell.BooleanCellValue.ToString();
                        default:
                            return "";
                    }
                }
                catch
                {
                    wl.WriteLogs("无法获取公式结果，返回空值");
                    return "";
                }
            }
        }

        /// <summary>
        /// 填充Rd10数据
        /// </summary>
        /// <param name="rd10View">Rd10视图对象</param>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        private static void FillRd10Data(WanJi_Rd10_View rd10View, IRow row, Dictionary<string, int> columnMapping)
        {
            try
            {
                // 根据Excel模板的列映射到WanJi_Rd10_View的字段
                
                // 基础信息
                rd10View.cMoCode = GetCellValue(row, columnMapping, "工单");
                rd10View.cXianBie = GetCellValue(row, columnMapping, "线别");
                rd10View.cJiZhongName = GetCellValue(row, columnMapping, "机种");
                rd10View.cBanCi = GetCellValue(row, columnMapping, "班别");
                rd10View.cPlanCode = GetCellValue(row, columnMapping, "排产计划编号");
                rd10View.cInvCode = GetCellValue(row, columnMapping, "U8编码");
                // 处理日期
                string dateValue = GetCellValue(row, columnMapping, "日期");
                if (!string.IsNullOrEmpty(dateValue))
                {
                    DateTime parsedDate;
                    if (DateTime.TryParse(dateValue, out parsedDate))
                    {
                        rd10View.dDate = parsedDate;
                    }
                    else
                    {
                        rd10View.dDate = DateTime.MinValue;
                    }
                }
                else
                {
                    rd10View.dDate = DateTime.MinValue;
                }

                // 处理实际生产数
                string qtyValue = GetCellValue(row, columnMapping, "实际生产数");
                if (!string.IsNullOrEmpty(qtyValue))
                {
                    string numericValue = Regex.Replace(qtyValue, @"[^\d.]", "");
                    int parsedQty;
                    if (int.TryParse(numericValue, out parsedQty))
                    {
                        rd10View.iQuantity = parsedQty;
                    }
                    else
                    {
                        rd10View.iQuantity = 0;
                    }
                }
                else
                {
                    rd10View.iQuantity = 0;
                }

                // 处理线上人员数
                string workerQtyValue = GetCellValue(row, columnMapping, "线上");
                if (!string.IsNullOrEmpty(workerQtyValue))
                {
                    decimal parsedValue;
                    if (decimal.TryParse(workerQtyValue, out parsedValue))
                    {
                        rd10View.dworkerqty = parsedValue;
                    }
                }

                // 处理线外人员数
                string indirectQtyValue = GetCellValue(row, columnMapping, "线外");
                if (!string.IsNullOrEmpty(indirectQtyValue))
                {
                    decimal parsedValue;
                    if (decimal.TryParse(indirectQtyValue, out parsedValue))
                    {
                        rd10View.dindirectqty = parsedValue;
                    }
                }

                // 处理线体数（从"线体"列获取或单独处理）
                string xianQtyValue = GetCellValue(row, columnMapping, "线体");
                if (!string.IsNullOrEmpty(xianQtyValue))
                {
                    // 如果线体列包含数字信息，提取数字部分
                    string numericPart = Regex.Replace(xianQtyValue, @"[^\d.]", "");
                    if (!string.IsNullOrEmpty(numericPart))
                    {
                        decimal parsedValue;
                        if (decimal.TryParse(numericPart, out parsedValue))
                        {
                            rd10View.dxianqty = parsedValue;
                        }
                    }
                }

                // 处理设备工时（时间）
                string machineHrValue = GetCellValue(row, columnMapping, "时间");
                if (!string.IsNullOrEmpty(machineHrValue))
                {
                    decimal parsedValue;
                    if (decimal.TryParse(machineHrValue, out parsedValue))
                    {
                        rd10View.dmachinehr = parsedValue;
                    }
                }

                // 处理人工工时
                string workHrValue = GetCellValue(row, columnMapping, "工时");
                if (!string.IsNullOrEmpty(workHrValue))
                {
                    decimal parsedValue;
                    if (decimal.TryParse(workHrValue, out parsedValue))
                    {
                        rd10View.dworkhr = parsedValue;
                    }
                }

                // 初始化默认值
                rd10View.chk = false;
                rd10View.bU8 = false;
                rd10View.bU8GS = false;
                rd10View.cImportResult = "";
            }
            catch (Exception ex)
            {
                wl.WriteLogs("填充Rd10数据时发生错误: " + ex.Message);
                throw;
            }
        }

        /// <summary>
        /// 获取Excel文件中所有工作表的名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>工作表名称列表</returns>
        public static List<string> GetSheetNames(string filePath)
        {
            List<string> sheetNames = new List<string>();
            
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                for (int i = 0; i < workbook.NumberOfSheets; i++)
                {
                    sheetNames.Add(workbook.GetSheetName(i));
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs("获取工作表名称时发生错误: " + ex.Message);
                throw;
            }

            return sheetNames;
        }

        /// <summary>
        /// 验证Excel文件格式是否正确
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>验证结果信息</returns>
        public static string ValidateExcelFormat(string filePath)
        {
            try
            {
                IWorkbook workbook;
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(file);
                }

                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    return "Excel文件中没有工作表";
                }

                // 检查是否有足够的行数
                if (sheet.LastRowNum < 1)
                {
                    return "Excel文件数据不足，至少需要2行（标题行+数据行）";
                }

                // 检查第二行是否有标题
                IRow headerRow = sheet.GetRow(1);
                if (headerRow == null)
                {
                    return "第二行（标题行）不存在";
                }

                //第一行的标题
                IRow headerRow0 = sheet.GetRow(0);
                // 解析标题行，如果第二行是空，取第一行的标题
                Dictionary<string, int> columnMapping = ParseHeaderRow(headerRow, headerRow0);
                
                // 验证必需列
                try
                {
                    ValidateRequiredColumns(columnMapping);
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }

                return "Excel格式验证通过";
            }
            catch (Exception ex)
            {
                wl.WriteLogs("验证Excel格式时发生错误: " + ex.Message);
                return "验证Excel格式失败：" + ex.Message;
            }
        }

        /// <summary>
        /// 获取合并单元格的值（支持合并单元格）
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="row">数据行</param>
        /// <param name="columnMapping">列名映射</param>
        /// <param name="columnName">列名</param>
        /// <returns>单元格值</returns>
        private static string GetMergedCellValue(ISheet sheet, IRow row, Dictionary<string, int> columnMapping, string columnName)
        {
            if (!columnMapping.ContainsKey(columnName))
            {
                return "";
            }

            int columnIndex = columnMapping[columnName];
            ICell cell = row.GetCell(columnIndex);

            if (cell == null)
            {
                return "";
            }

            // 检查是否是合并单元格
            for (int i = 0; i < sheet.NumMergedRegions; i++)
            {
                CellRangeAddress mergedRegion = sheet.GetMergedRegion(i);
                if (mergedRegion.IsInRange(row.RowNum, columnIndex))
                {
                    // 获取合并单元格的第一个单元格值
                    IRow firstRow = sheet.GetRow(mergedRegion.FirstRow);
                    if (firstRow != null)
                    {
                        ICell firstCell = firstRow.GetCell(mergedRegion.FirstColumn);
                        if (firstCell != null)
                        {
                            return GetCellValueAsString(firstCell);
                        }
                    }
                }
            }

            // 如果不是合并单元格，直接获取值
            return GetCellValueAsString(cell);
        }
        /// <summary>
        /// 获取单元格的字符串值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>字符串值</returns>
        private static string GetCellValueAsString(ICell cell)
        {
            if (cell == null)
            {
                return "";
            }

            try
            {
                switch (cell.CellType)
                {
                    case CellType.STRING:
                        return cell.StringCellValue.Trim();
                    case CellType.NUMERIC:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            return cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            return cell.NumericCellValue.ToString();
                        }
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue.ToString();
                    case CellType.FORMULA:
                        return GetFormulaValue(cell);
                    case CellType.BLANK:
                        return "";
                    case CellType.ERROR:
                        wl.WriteLogs(string.Format("单元格错误: {0}", cell.ErrorCellValue));
                        return "";
                    default:
                        return "";
                }
            }
            catch (Exception ex)
            {
                wl.WriteLogs(string.Format("获取单元格值错误: {0}", ex.Message));
                return "";
            }
        }
    }
}